#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
Project Name: cooperation_proj
Version: 
File:    
Author: RoyceCAI
Created Date: 2025-11-03
Description: 
"""

"""
MySQL安全审计规则定义
用于检查MySQL数据库的安全配置
"""

import subprocess
import re
import os
from typing import Dict, List, Any
import logging

logger = logging.getLogger(__name__)


class MySQLAuditRules:
    """MySQL安全审计规则类"""

    HOST = "localhost"
    USERNAME = "root"
    PORT = 3307
    PASSWORD = "666888"

    @staticmethod
    def run_mysql_command(command: str, host: str = None, username: str = None, port: int = None,
                          password: str = None) -> str:
        """执行MySQL命令"""

        if host is None:
            host = MySQLAuditRules.HOST
        if port is None:
            port = MySQLAuditRules.PORT
        if username is None:
            username = MySQLAuditRules.USERNAME
        if password is None:
            password = MySQLAuditRules.PASSWORD

        try:
            # 构建MySQL连接参数
            auth_args = f"-u{username}"
            if password:
                auth_args += f" -p{password}"

            # 执行MySQL命令
            cmd = f"mysql {auth_args} -h {host} -P {port} -e \"{command}\" 2>/dev/null"
            result = subprocess.run(
                cmd,
                shell=True,
                capture_output=True,
                timeout=30,
                text=True
            )
            return result.stdout.strip() if result.stdout else result.stderr.strip()
        except Exception as e:
            return f"Error: {str(e)}"

    @staticmethod
    def get_mysql_config_file() -> str:
        """获取MySQL配置文件路径"""
        possible_paths = [
            "/etc/my.cnf",
            "/etc/mysql/my.cnf",
            "/usr/etc/my.cnf",
            "~/.my.cnf",
            "/etc/mysql/mysql.conf.d/mysqld.cnf",
            "C:\\ProgramData\\MySQL\\MySQL Server 8.0\\my.ini",
            "C:\\Program Files\\MySQL\\MySQL Server 8.0\\my.ini"
        ]

        for path in possible_paths:
            expanded_path = os.path.expanduser(path)
            if os.path.exists(expanded_path):
                return expanded_path
        return ""

    @staticmethod
    def check_password_policy() -> Dict[str, Any]:
        """1. 检查密码策略"""
        print("[规则1] 检查MySQL密码策略...")
        status_details = []
        issues = []

        # 检查密码复杂度要求
        complexity_query = "SHOW VARIABLES LIKE 'validate_password%';"
        complexity_output = MySQLAuditRules.run_mysql_command(complexity_query)

        if "validate_password.policy" in complexity_output:
            policy_match = re.search(r"validate_password\.policy\s+(\w+)", complexity_output)
            policy = policy_match.group(1) if policy_match else "未设置"

            if policy in ["MEDIUM", "STRONG"]:
                status_details.append(f"✓ 密码策略强度: {policy}(符合要求)")
            else:
                issues.append(f"密码策略强度不足: {policy}")
                status_details.append(f"✗✗ 密码策略强度: {policy}(建议MEDIUM或STRONG)")

        # 检查密码长度要求
        length_match = re.search(r"validate_password\.length\s+(\d+)", complexity_output)
        min_length = int(length_match.group(1)) if length_match else 0

        if min_length >= 8:
            status_details.append(f"✓ 最小密码长度: {min_length}位")
        else:
            issues.append(f"密码长度不足: {min_length}位")
            status_details.append(f"✗✗ 最小密码长度: {min_length}位(建议至少8位)")

        # 检查是否启用密码验证插件
        plugin_query = "SHOW PLUGINS;"
        plugin_output = MySQLAuditRules.run_mysql_command(plugin_query)

        if "validate_password" in plugin_output and "ACTIVE" in plugin_output:
            status_details.append("✓ 密码验证插件: 已启用")
        else:
            issues.append("未启用密码验证插件")
            status_details.append("✗✗ 密码验证插件: 未启用")

        return {
            "rule_name": "MySQL密码策略检查",
            "status": "通过" if not issues else "未通过",
            "config_status": {
                "password_policy": policy if 'policy' in locals() else "未设置",
                "min_password_length": min_length,
                "validate_password_plugin": "validate_password" in plugin_output and "ACTIVE" in plugin_output
            },
            "status_details": status_details,
            "recommendation": "密码策略符合要求" if not issues else f"需修复: {'; '.join(issues)}"
        }

    @staticmethod
    def check_user_accounts() -> Dict[str, Any]:
        """2. 检查用户账户安全"""
        print("[规则2] 检查MySQL用户账户...")
        status_details = []
        issues = []

        # 检查用户账户和权限
        user_query = """
        SELECT user, host, authentication_string, password_expired, account_locked 
        FROM mysql.user 
        WHERE user NOT IN ('mysql.sys', 'mysql.session', 'mysql.infoschema');
        """
        user_output = MySQLAuditRules.run_mysql_command(user_query)

        # 分析用户账户
        if user_output and "user" in user_output:
            lines = user_output.strip().split('\n')
            user_count = len(lines) - 1  # 减去标题行

            status_details.append(f"✓ 总用户账户数: {user_count}")

            # 检查空密码账户
            empty_pass_query = "SELECT user, host FROM mysql.user WHERE authentication_string = '' OR authentication_string IS NULL;"
            empty_pass_output = MySQLAuditRules.run_mysql_command(empty_pass_query)

            if empty_pass_output and "user" in empty_pass_output and len(empty_pass_output.split('\n')) > 1:
                issues.append("存在空密码账户")
                status_details.append("✗✗ 空密码账户: 存在")
                for line in empty_pass_output.split('\n')[1:]:
                    if line.strip():
                        status_details.append(f"  - {line.strip()}")
            else:
                status_details.append("✓ 空密码账户: 无")

            # 检查远程root账户
            remote_root_query = "SELECT user, host FROM mysql.user WHERE user = 'root' AND host != 'localhost';"
            remote_root_output = MySQLAuditRules.run_mysql_command(remote_root_query)

            if remote_root_output and "user" in remote_root_output and len(remote_root_output.split('\n')) > 1:
                issues.append("存在远程root账户")
                status_details.append("✗✗ 远程root账户: 存在")
            else:
                status_details.append("✓ 远程root账户: 无")

        return {
            "rule_name": "MySQL用户账户检查",
            "status": "正常" if not issues else "异常",
            "config_status": {
                "total_users": user_count if 'user_count' in locals() else 0,
                "empty_password_accounts": empty_pass_output if 'empty_pass_output' in locals() else "",
                "remote_root_accounts": remote_root_output if 'remote_root_output' in locals() else ""
            },
            "status_details": status_details,
            "recommendation": "用户账户配置正常" if not issues else "建议删除空密码账户和限制root远程访问"
        }

    @staticmethod
    def check_network_config() -> Dict[str, Any]:
        """3. 检查网络配置安全"""
        print("[规则3] 检查MySQL网络配置...")
        status_details = []
        issues = []

        # 检查绑定地址
        bind_query = "SHOW VARIABLES LIKE 'bind_address';"
        bind_output = MySQLAuditRules.run_mysql_command(bind_query)
        bind_match = re.search(r"bind_address\s+([^\s]+)", bind_output)
        bind_address = bind_match.group(1) if bind_match else "未找到"

        if bind_address == "127.0.0.1":
            status_details.append("✓ 绑定地址: 127.0.0.1(仅本地访问)")
        else:
            issues.append(f"绑定地址不安全: {bind_address}")
            status_details.append(f"✗✗ 绑定地址: {bind_address}(建议设置为127.0.0.1)")

        # 检查端口配置
        port_query = "SHOW VARIABLES LIKE 'port';"
        port_output = MySQLAuditRules.run_mysql_command(port_query)
        port_match = re.search(r"port\s+(\d+)", port_output)
        port = port_match.group(1) if port_match else "3306"

        if port == "3306":
            status_details.append("⚠️ 使用默认端口: 3306(建议修改)")
        else:
            status_details.append(f"✓ 使用非默认端口: {port}")

        # 检查跳过网络配置
        skip_networking_query = "SHOW VARIABLES LIKE 'skip_networking';"
        skip_networking_output = MySQLAuditRules.run_mysql_command(skip_networking_query)
        skip_match = re.search(r"skip_networking\s+(\w+)", skip_networking_output)
        skip_networking = skip_match.group(1) if skip_match else "OFF"

        if skip_networking == "ON":
            status_details.append("✓ 跳过网络: 已启用(增强安全)")
        else:
            status_details.append("ℹ️ 跳过网络: 未启用")

        return {
            "rule_name": "MySQL网络配置检查",
            "status": "正常" if bind_address == "127.0.0.1" else "异常",
            "config_status": {
                "bind_address": bind_address,
                "port": port,
                "skip_networking": skip_networking
            },
            "status_details": status_details,
            "recommendation": "网络配置安全" if bind_address == "127.0.0.1" else "建议将bind_address设置为127.0.0.1并修改默认端口"
        }

    @staticmethod
    def check_ssl_config() -> Dict[str, Any]:
        """4. 检查SSL配置"""
        print("[规则4] 检查MySQL SSL配置...")
        status_details = []
        issues = []

        # 检查SSL状态
        ssl_query = "SHOW VARIABLES LIKE '%ssl%';"
        ssl_output = MySQLAuditRules.run_mysql_command(ssl_query)

        have_ssl_match = re.search(r"have_ssl\s+(\w+)", ssl_output)
        have_ssl = have_ssl_match.group(1) if have_ssl_match else "DISABLED"

        if have_ssl == "YES":
            status_details.append("✓ SSL支持: 已启用")
        else:
            issues.append("SSL未启用")
            status_details.append("✗✗ SSL支持: 未启用")

        # 检查是否要求SSL连接
        require_secure_transport_query = "SHOW VARIABLES LIKE 'require_secure_transport';"
        require_secure_output = MySQLAuditRules.run_mysql_command(require_secure_transport_query)
        require_match = re.search(r"require_secure_transport\s+(\w+)", require_secure_output)
        require_secure = require_match.group(1) if require_match else "OFF"

        if require_secure == "ON":
            status_details.append("✓ 强制安全传输: 已启用")
        else:
            issues.append("未强制要求安全传输")
            status_details.append("✗✗ 强制安全传输: 未启用")

        return {
            "rule_name": "MySQL SSL配置检查",
            "status": "通过" if have_ssl == "YES" and require_secure == "ON" else "未通过",
            "config_status": {
                "ssl_enabled": have_ssl == "YES",
                "require_secure_transport": require_secure == "ON"
            },
            "status_details": status_details,
            "recommendation": "SSL配置符合要求" if have_ssl == "YES" and require_secure == "ON" else "建议启用SSL并配置require_secure_transport=ON"
        }

    @staticmethod
    def check_audit_logging() -> Dict[str, Any]:
        """5. 检查审计日志配置"""
        print("[规则5] 检查MySQL审计日志...")
        status_details = []
        issues = []

        # 检查通用日志
        general_log_query = "SHOW VARIABLES LIKE 'general_log%';"
        general_log_output = MySQLAuditRules.run_mysql_command(general_log_query)

        general_log_match = re.search(r"general_log\s+(\w+)", general_log_output)
        general_log = general_log_match.group(1) if general_log_match else "OFF"

        if general_log == "ON":
            status_details.append("✓ 通用查询日志: 已启用")
        else:
            status_details.append("ℹ️ 通用查询日志: 未启用(生产环境可关闭)")

        # 检查慢查询日志
        slow_query_query = "SHOW VARIABLES LIKE 'slow_query_log%';"
        slow_query_output = MySQLAuditRules.run_mysql_command(slow_query_query)

        slow_log_match = re.search(r"slow_query_log\s+(\w+)", slow_query_output)
        slow_log = slow_log_match.group(1) if slow_log_match else "OFF"

        if slow_log == "ON":
            status_details.append("✓ 慢查询日志: 已启用")
        else:
            issues.append("慢查询日志未启用")
            status_details.append("✗✗ 慢查询日志: 未启用")

        # 检查二进制日志
        binlog_query = "SHOW VARIABLES LIKE 'log_bin%';"
        binlog_output = MySQLAuditRules.run_mysql_command(binlog_query)

        binlog_match = re.search(r"log_bin\s+(\w+)", binlog_output)
        binlog = binlog_match.group(1) if binlog_match else "OFF"

        if binlog == "ON":
            status_details.append("✓ 二进制日志: 已启用")
        else:
            issues.append("二进制日志未启用")
            status_details.append("✗✗ 二进制日志: 未启用")

        return {
            "rule_name": "MySQL审计日志检查",
            "status": "通过" if slow_log == "ON" and binlog == "ON" else "未通过",
            "config_status": {
                "general_log": general_log == "ON",
                "slow_query_log": slow_log == "ON",
                "binary_log": binlog == "ON"
            },
            "status_details": status_details,
            "recommendation": "审计日志配置合理" if slow_log == "ON" and binlog == "ON" else "建议启用慢查询日志和二进制日志"
        }

    @staticmethod
    def check_file_permissions() -> Dict[str, Any]:
        """6. 检查文件权限安全"""
        print("[规则6] 检查MySQL文件权限...")
        status_details = []
        issues = []

        # 获取数据目录
        datadir_query = "SHOW VARIABLES LIKE 'datadir';"
        datadir_output = MySQLAuditRules.run_mysql_command(datadir_query)
        datadir_match = re.search(r"datadir\s+([^\s]+)", datadir_output)
        datadir = datadir_match.group(1) if datadir_match else ""

        if datadir and os.path.exists(datadir):
            # 检查数据目录权限
            try:
                stat_info = os.stat(datadir)
                permissions = oct(stat_info.st_mode)[-3:]

                if permissions <= "700":
                    status_details.append(f"✓ 数据目录权限: {permissions}(安全)")
                else:
                    issues.append(f"数据目录权限过松: {permissions}")
                    status_details.append(f"✗✗ 数据目录权限: {permissions}(建议700或更严格)")
            except:
                status_details.append("⚠️ 无法读取数据目录权限")

        # 检查配置文件权限
        config_file = MySQLAuditRules.get_mysql_config_file()
        if config_file and os.path.exists(config_file):
            try:
                stat_info = os.stat(config_file)
                permissions = oct(stat_info.st_mode)[-3:]

                if permissions <= "600":
                    status_details.append(f"✓ 配置文件权限: {permissions}(安全)")
                else:
                    issues.append(f"配置文件权限过松: {permissions}")
                    status_details.append(f"✗✗ 配置文件权限: {permissions}(建议600)")
            except:
                status_details.append("⚠️ 无法读取配置文件权限")

        return {
            "rule_name": "MySQL文件权限检查",
            "status": "正常" if not issues else "异常",
            "config_status": {
                "datadir_permissions": permissions if 'permissions' in locals() else "未知",
                "config_file_found": bool(config_file)
            },
            "status_details": status_details,
            "recommendation": "文件权限配置合理" if not issues else "建议收紧文件权限设置"
        }

    @staticmethod
    def check_plugin_security() -> Dict[str, Any]:
        """7. 检查插件安全"""
        print("[规则7] 检查MySQL插件安全...")
        status_details = []
        issues = []

        # 检查已加载的插件
        plugins_query = "SHOW PLUGINS;"
        plugins_output = MySQLAuditRules.run_mysql_command(plugins_query)

        # 检查危险插件
        dangerous_plugins = ["libmysql", "auth_socket"]
        loaded_dangerous = []

        for plugin in dangerous_plugins:
            if plugin in plugins_output and "ACTIVE" in plugins_output:
                loaded_dangerous.append(plugin)
                status_details.append(f"✗✗ 危险插件: {plugin}已加载")

        if not loaded_dangerous:
            status_details.append("✓ 危险插件: 未发现危险插件")

        return {
            "rule_name": "MySQL插件安全检查",
            "status": "正常" if not loaded_dangerous else "异常",
            "config_status": {
                "dangerous_plugins_loaded": loaded_dangerous
            },
            "status_details": status_details,
            "recommendation": "插件配置安全" if not loaded_dangerous else "建议卸载不必要的危险插件"
        }

    @staticmethod
    def check_version_security() -> Dict[str, Any]:
        """8. 检查版本安全"""
        print("[规则8] 检查MySQL版本安全...")
        status_details = []

        # 获取版本信息
        version_query = "SELECT VERSION();"
        version_output = MySQLAuditRules.run_mysql_command(version_query)

        version_match = re.search(r"(\d+\.\d+\.\d+)", version_output)
        version = version_match.group(1) if version_match else "未知"

        status_details.append(f"✓ MySQL版本: {version}")

        # 简单版本检查（实际应使用CVE数据库）
        major_version = int(version.split('.')[0]) if version != "未知" else 0
        if major_version >= 8:
            status_details.append("✓ 版本较新(MySQL 8.0+)")
        else:
            status_details.append("⚠️ 版本较旧，建议升级")

        return {
            "rule_name": "MySQL版本安全检查",
            "status": "正常",
            "config_status": {
                "version": version,
                "is_latest": major_version >= 8
            },
            "status_details": status_details,
            "recommendation": "建议定期检查安全更新" if major_version >= 8 else "建议升级到最新版本"
        }

    @staticmethod
    def get_all_rules() -> List[callable]:
        """获取所有审计规则的函数列表"""
        return [
            MySQLAuditRules.check_password_policy,
            MySQLAuditRules.check_user_accounts,
            MySQLAuditRules.check_network_config,
            MySQLAuditRules.check_ssl_config,
            MySQLAuditRules.check_audit_logging,
            MySQLAuditRules.check_file_permissions,
            MySQLAuditRules.check_plugin_security,
            MySQLAuditRules.check_version_security
        ]
